package com.thinkingstar.iads.datacenter.entity.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.formula.functions.T;
import org.nutz.castor.castor.String2SqlDate;
import org.nutz.dao.Dao;
import org.nutz.dao.QueryResult;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.util.NutMap;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bstek.urule.model.rule.lhs.And;
import com.fasterxml.jackson.databind.util.JSONPObject;
import com.thinkingstar.iads.common.jsonUtils.AjaxResult;
import com.thinkingstar.iads.common.serviceImpl.EntityRepositoryServiceImpl;
import com.thinkingstar.iads.common.utils.InitSystemProperties;
import com.thinkingstar.iads.common.utils.StringUtil;
import com.thinkingstar.iads.common.utils.SwapUtil;
import com.thinkingstar.iads.cs.sys.enumerate.CODE_TYPE;

@IocBean(args = {"refer:dao"})
public class DaoDcFormField extends EntityRepositoryServiceImpl<T>{

	public DaoDcFormField(Dao dao) {
		super(dao);
	}
	
	/**
	 * 删除某个表结构
	 * @param table_name
	 */
	public void dropTable(String table_name){
		String drop_sql = "DROP TABLE IF EXISTS "+table_name;
		Sql sql = Sqls.create(drop_sql);
		dao().execute(sql);
	}
	/**
	 * 获取某个表的所有表头信息
	 * @return
	 */
	public List <NutMap> queryHradListByTable(String table_name){
		if(StringUtils.isBlank(table_name)){
			return null;
		}
		List <NutMap> list = null;
		StringBuffer find_sql = new StringBuffer("select CASE WHEN COLUMN_NAME = 'serial_number' THEN '1' ELSE '0' END flag ,  "
				+ " ifnull(COLUMN_NAME,'') field_name , ifnull(IS_NULLABLE,'') is_null , ifnull(COLUMN_TYPE,'') column_type , "
				+ " ifnull(DATA_TYPE,'') field_type , ifnull(CHARACTER_MAXIMUM_LENGTH,'') field_length , ifnull(COLUMN_DEFAULT,'') field_default , ifnull(COLUMN_COMMENT,'') field_comment "
				+ " from information_schema.COLUMNS where table_name = @table_name  and table_schema = @table_schema and COLUMN_NAME <> 'id' and  COLUMN_NAME <> 'project_id' "
				+ " order by flag desc ");
		Sql sql = Sqls.create(find_sql.toString());
		sql.params().set("table_name", table_name);
		String table_schema = InitSystemProperties.getTableSchema();
		sql.params().set("table_schema", table_schema);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <Map> list = new LinkedList<>();
				while(rs.next()){
					NutMap  map = new NutMap();
					map.put("field_name", rs.getString("field_name"));
					map.put("is_null", rs.getString("is_null"));
					map.put("field_type", rs.getString("field_type"));
					map.put("field_length", rs.getString("field_length"));
					map.put("field_default", rs.getString("field_default"));
					map.put("field_comment", rs.getString("field_comment"));
					map.put("column_type", rs.getString("column_type"));
					list.add(map);
				}
				SwapUtil.clearNull(list);
				return list;
			}
		});
		dao().execute(sql);
		list = sql.getList(NutMap.class);
		return list;
	}
	/**
	 * 按字段列表 统计这些字段
	 * */
	public List<Map> countField(List<NutMap> fieldList,String table_name,String project_id){
		String sql =" select ";
		for (int i=0;i<fieldList.size();i++) {
			if (fieldList.get(i).get("field_type").equals("int")||fieldList.get(i).get("field_type").equals("double")||fieldList.get(i).get("field_type").equals("float")) {
				sql+=" sum("+fieldList.get(i).get("field_name")+") as "+fieldList.get(i).get("field_name")+",";
			}
		}
		if (!sql.contains("sum")) {
			//无需要统计的数据
			return new ArrayList<Map>();
		}

		if (sql.endsWith(",")) {
			sql = sql.substring(0,sql.lastIndexOf(","));
		}
		sql+=" from "+table_name+" where project_id='"+project_id+"'";

		Sql sqls = Sqls.create(sql);
		sqls.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<Map<String,Object>> list = new ArrayList<>();
				while (rs.next()){
					Map<String,Object> map = new HashMap<>();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						String value = rs.getString(filed);

						if (StringUtils.isNotEmpty(value) && value.contains(".") && value.substring(value.indexOf("."),value.length()-1).length()>2){
							value = value.substring(0,value.lastIndexOf(".")+2);
						}
						if(null!=value) {
							map.put(filed, value);
						}

					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sqls);
		List<Map> mapList = sqls.getList(Map.class);
		return mapList;
	}

	/**
	 * 按字段列表 统计这些字段  带查询条件
	 * */
	public List<Map> countFieldByParam(List<NutMap> fieldList,NutMap param){
		String sql =" select ";
		for (int i=0;i<fieldList.size();i++) {
			if (fieldList.get(i).get("field_type").equals("int")||fieldList.get(i).get("field_type").equals("decimal")||fieldList.get(i).get("field_type").equals("double")||fieldList.get(i).get("field_type").equals("float")) {
				sql+=" sum("+fieldList.get(i).get("field_name")+") as "+fieldList.get(i).get("field_name")+",";
			}
		}
		if (!sql.contains("sum")) {
			//无需要统计的数据
			return new ArrayList<Map>();
		}

		if (sql.endsWith(",")) {
			sql = sql.substring(0,sql.lastIndexOf(","));
		}
		sql+=" from "+param.get("table_name")+" where project_id='"+param.get("project_id")+"'";

		for (int i=0;i<fieldList.size();i++) {
			String field = fieldList.get(i).get("field_name").toString();
			if (!field.equals("id") && !field.equals("project_id")){
				if (fieldList.get(i).get("widget_type").equals("select")) {
					if(param.containsKey(field)) {
						String p_s = (String)param.get(field);
						if (StringUtils.isNotEmpty(param.get(field).toString())) {
							sql += " and "+field +" ='"+param.get(field).toString()+"' ";
						}
					}
				}
			}
		}
		Sql sqls = Sqls.create(sql);
		sqls.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<Map<String,Object>> list = new ArrayList<>();
				while (rs.next()){
					Map<String,Object> map = new HashMap<>();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						String value = rs.getString(filed);

						if (StringUtils.isNotEmpty(value) && value.contains(".") && value.substring(value.indexOf("."),value.length()-1).length()>2){
							value = value.substring(0,value.lastIndexOf(".")+2);
						}
						if(null!=value) {
							map.put(filed, value);
						}

					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sqls);
		List<Map> mapList = sqls.getList(Map.class);
		return mapList;
	}

	/**
	 * 获取某个表的所有字段信息(数据项名称,数据项类型,数据项长度,注释)
	 * @return
	 */
	public List <NutMap> queryColumnList(String table_name){
		if(StringUtils.isBlank(table_name)){
			return null;
		}
		List <NutMap> list = null;
		StringBuffer find_sql = new StringBuffer("select CASE WHEN COLUMN_NAME = 'serial_number' THEN '1' ELSE '0' END flag , "
				+ " ifnull(COLUMN_NAME,'') field_name , ifnull(IS_NULLABLE,'') is_null , ifnull(COLUMN_TYPE,'') column_type , "
				+ " ifnull(DATA_TYPE,'') field_type , ifnull(CHARACTER_MAXIMUM_LENGTH,'') field_length , ifnull(COLUMN_DEFAULT,'') field_default , ifnull(COLUMN_COMMENT,'') field_comment "
				+ " from information_schema.COLUMNS where table_name = @table_name and table_schema = @table_schema"
				+ " order by flag desc " );
		Sql sql = Sqls.create(find_sql.toString());
		sql.params().set("table_name", table_name);
		String table_schema = InitSystemProperties.getTableSchema();
		sql.params().set("table_schema", table_schema);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <Map> list = new LinkedList<>();
				while(rs.next()){
					NutMap  map = new NutMap();
					map.put("field_name", rs.getString("field_name"));
					map.put("is_null", rs.getString("is_null"));
					map.put("field_type", rs.getString("field_type"));
					map.put("field_length", rs.getString("field_length"));
					map.put("field_default", rs.getString("field_default"));
					map.put("field_comment", rs.getString("field_comment"));
					map.put("column_type", rs.getString("column_type"));
					list.add(map);
				}
				SwapUtil.clearNull(list);
				return list;
			}
		});
		dao().execute(sql);
		list = sql.getList(NutMap.class);
		return list;
	}

	/**
	 *  设计单元  获取某个表的所有字段信息(数据项名称,数据项类型,数据项长度,注释，表单类型（控件类型）判断是否显示)
	 * @return
	 */
	public List <NutMap> queryColumnListForDesignCell(String table_name){
		if(StringUtils.isBlank(table_name)){
			return null;
		}
		List <NutMap> list = null;
		StringBuffer find_sql = new StringBuffer("select CASE WHEN COLUMN_NAME = 'serial_number' THEN '1' ELSE '0' END flag , "
				+ " ifnull(COLUMN_NAME,'') field_name , ifnull(IS_NULLABLE,'') is_null , ifnull(COLUMN_TYPE,'') column_type , "
				+ " ifnull(DATA_TYPE,'') field_type , ifnull(CHARACTER_MAXIMUM_LENGTH,'') field_length , ifnull(COLUMN_DEFAULT,'') field_default , ifnull(COLUMN_COMMENT,'') field_comment,b.widget_type "
				+ " from information_schema.COLUMNS a , dc_project_widget b where a.COLUMN_NAME=b.field_name and a.table_name = @table_name and a.TABLE_NAME = b.table_name and table_schema = @table_schema"
				+ " order by flag desc " );
		Sql sql = Sqls.create(find_sql.toString());
		sql.params().set("table_name", table_name);
		String table_schema = InitSystemProperties.getTableSchema();
		sql.params().set("table_schema", table_schema);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <Map> list = new LinkedList<>();
				while(rs.next()){
					NutMap  map = new NutMap();
					map.put("field_name", rs.getString("field_name"));
					map.put("is_null", rs.getString("is_null"));
					map.put("field_type", rs.getString("field_type"));
					map.put("field_length", rs.getString("field_length"));
					map.put("field_default", rs.getString("field_default"));
					map.put("field_comment", rs.getString("field_comment"));
					map.put("column_type", rs.getString("column_type"));
					map.put("widget_type", rs.getString("widget_type"));
					list.add(map);
				}
				NutMap  map = new NutMap();
				map.put("field_name", "id");
				map.put("is_null", "NO");
				map.put("field_type", "varchar");
				map.put("field_length", "32");
				map.put("field_default", "");
				map.put("field_comment", "主键");
				map.put("column_type", "varchar(32)");
				map.put("widget_type", "inner_text");
				list.add(map);
				SwapUtil.clearNull(list);
				return list;
			}
		});
		dao().execute(sql);
		list = sql.getList(NutMap.class);
		return list;
	}

	
	/**
	 * 根据表名判断表是否存在
	 * @param table_name
	 * @return
	 */
	public Boolean judgeTable(String table_name){
		if(StringUtils.isBlank(table_name)){
			return false;
		}
		String find_sql = "SELECT table_name FROM information_schema.TABLES WHERE table_name = @table_name and table_schema = @table_schema ";
		Sql sql = Sqls.create(find_sql);
		sql.params().set("table_name", table_name);
		String table_schema = InitSystemProperties.getTableSchema();
		sql.params().set("table_schema", table_schema);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				String table = "";
				while(rs.next()){
					table = rs.getString("table_name");
				}
				return table;
			}
		});
		dao().execute(sql);
		String result_str = sql.getString();
		return StringUtils.isNotBlank(result_str)?true:false;
	}
	
	/**
	 * 通用建表方法
	 * @param table_name
	 * @return
	 */
	public Boolean createTable(String table_name){
		try {
			if(StringUtils.isBlank(table_name)){
				return false;
			}
			String create_sql = " create table @table_name ( id int not null auto_increment , primary key (id)) ";
			Sql sql = Sqls.create(create_sql);
			sql.vars().set("table_name", table_name);
			dao().execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	/**
	 * 删除字段
	 * @return
	 */
	public Boolean delTableField(String table_name , String field_name){
		if(!StringUtil.commenVerifyStr(field_name,table_name)){
			return false;
		}
		if(judgeTable(table_name)){
			String drop_sql = " alter table "+table_name+" drop "+field_name;
			Sql sql = Sqls.create(drop_sql);
			dao().execute(sql);
			return true;
		}
		return false;
	}
	
	
	/**
	 * 某个表的某个字段是否存在
	 * @return
	 */
	public Boolean judgeTableField(String table_name , String field_name){
		if(!StringUtil.commenVerifyStr(table_name,field_name)){
			return false;
		}
		String find_sql = "select column_name from information_schema.columns WHERE table_name = @table_name AND column_name = @column_name and table_schema = @table_schema ";
		Sql sql = Sqls.create(find_sql);
		sql.params().set("table_name", table_name);
		sql.params().set("column_name", field_name);
		String table_schema = InitSystemProperties.getTableSchema();
		sql.params().set("table_schema", table_schema);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				String result_str = "";
				while(rs.next()){
					result_str = rs.getString("column_name");
				}
				return result_str;
			}
		});
		dao().execute(sql);
		String result_str = sql.getString();
		return StringUtils.isNotBlank(result_str)?true:false;
	}
	
	//添加字段
	public Boolean addTableField(String table_name , JSONObject jsonObject){
		try {
			String field_name = jsonObject.getString("field_name");
			String field_comment = jsonObject.getString("field_comment");
			String field_length = jsonObject.getString("field_length");
			String field_type = jsonObject.getString("field_type");
			String is_null = jsonObject.getString("is_null");//允许为空?
			String add_sql = "";
			if(CODE_TYPE.DOUBLE.getCode().equals(field_type)){
				add_sql = " alter table "+ table_name + " add "+field_name+" "+"decimal";
			}else{
				add_sql = " alter table "+ table_name + " add "+field_name+" "+field_type;
			}
			if(CODE_TYPE.INT.getCode().equals(field_type) || CODE_TYPE.VARCHAR.getCode().equals(field_type) || CODE_TYPE.DOUBLE.getCode().equals(field_type)){
				add_sql += " ("+field_length+")";
			}
			if("true".equals(is_null)){
				add_sql += " null ";
			}else{
				add_sql += " not null "; 
			}
			if(StringUtils.isNotBlank(field_comment)){
				add_sql += " comment '"+field_comment+"'";
			}
			Sql sql = Sqls.create(add_sql);
			dao().execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	//更新字段(只是更新字段类型)
	public Boolean updateTableField(String table_name , JSONObject jsonObject){
		try {
			String field_name = jsonObject.getString("field_name");
			String field_comment = jsonObject.getString("field_comment");
			String field_length = jsonObject.getString("field_length");
			String field_type = jsonObject.getString("field_type");
			String is_null = jsonObject.getString("is_null");//允许为空?
			String update_sql = "";
			if(CODE_TYPE.DOUBLE.getCode().equals(field_type)){
				update_sql = "alter table "+table_name+" modify column "+field_name +" "+"decimal";
			}else{
				update_sql = "alter table "+table_name+" modify column "+field_name +" "+field_type;
			}
			if(CODE_TYPE.INT.getCode().equals(field_type) || CODE_TYPE.VARCHAR.getCode().equals(field_type) || CODE_TYPE.DOUBLE.getCode().equals(field_type)){
				update_sql += " ("+field_length+")";
			}
			if("true".equals(is_null)){
				update_sql += " null ";
			}else{
				update_sql += " not null "; 
			}
			if(StringUtils.isNotBlank(field_comment)){
				update_sql += " comment '"+field_comment+"'";
			}
			Sql sql = Sqls.create(update_sql);
			dao().execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	
	/**
	 * 修改表结构
	 * @param table
	 * @param jsonArray
	 * @return
	 */
	public Boolean updateTableField(String table , JSONArray jsonArray){
		//先遍历所有字段信息找到需要改变的字段
		List <NutMap> list = queryColumnList(table);
		List <String> del_field_list = new ArrayList<>();
		List <JSONObject> add_field_list = new ArrayList<>();
		List <JSONObject> update_field_list = new ArrayList<>();
 		//过滤需要删除的字段
		for(Map map : list){
			String field_name = String.valueOf(map.get("field_name"));
			if("id".equals(field_name)){
				continue;
			}
			Boolean bool = true;//假设当前字段为删除的字段
			if(jsonArray != null && jsonArray.size() > 0){
				for(int i=0;i<jsonArray.size();i++){
					JSONObject jsonObject = jsonArray.getJSONObject(i);
					String new_field_name = jsonObject.getString("field_name");
					if(field_name.equals(new_field_name)){
						bool = false;//假设不成立
						break;
					}
				}
			}
			if(bool){
				del_field_list.add(field_name);//添加要删除的字段
			}
		}
		//过滤需要新增的字段
		for(int i=0;i<jsonArray.size();i++){
			JSONObject jsonObject = jsonArray.getJSONObject(i);
			String new_field_name = jsonObject.getString("field_name");
			Boolean bool = true;//假设该字段是需要新增的字段
			for(Map map : list){
				String field_name = String.valueOf(map.get("field_name"));
				if(new_field_name.equals(field_name)){
					bool = false;//假设不成立
					update_field_list.add(jsonObject);
					break;
				}
			}
			if(bool){
				add_field_list.add(jsonObject);
			}
		}
		
		//删除字段
		for(String del_field_name : del_field_list){
			delTableField(table,del_field_name);
		}
		
		//新增字段
		for(int i=0;i<add_field_list.size();i++){
			JSONObject jsonObject = add_field_list.get(i);
			addTableField(table,jsonObject);
		}
		
		//更新字段
		for(int i=0;i<update_field_list.size();i++){
			JSONObject jsonObject = update_field_list.get(i);
			updateTableField(table,jsonObject);
		}
		return true;
	}
	
	/**
	 * 添加表结构
	 * @return
	 */
	public Boolean addTableField(String table_name , JSONArray jsonArray){
		String create_sql = " create table "+table_name+" (id varchar(32)  comment '主键' , ";
		if(jsonArray != null && jsonArray.size() > 0){
			for(int i=0;i<jsonArray.size();i++){
				JSONObject jsonObject = jsonArray.getJSONObject(i);
				String field_name = jsonObject.getString("field_name");
				String field_comment = jsonObject.getString("field_comment");
				String field_length = jsonObject.getString("field_length");
				String field_type = jsonObject.getString("field_type");
				String is_null = jsonObject.getString("is_null"); //是否允许为空
				if(CODE_TYPE.TEXT.getCode().equals(field_type)){
					if("true".equals(is_null)){
						if(StringUtils.isNotBlank(field_comment)){
							create_sql +=  field_name+" "+field_type+" null comment '"+field_comment+"' ,";
						}else{
							create_sql += field_name+" "+field_type+" null";
						}
					}else{
						if(StringUtils.isNotBlank(is_null)){
							create_sql +=  field_name+" "+field_type+" not null comment '"+field_comment+"', ";
						}else{
							create_sql +=  field_name+" "+field_type+" not null";
						}
					}
				}else if(CODE_TYPE.DOUBLE.getCode().equals(field_type)){
					if("true".equals(is_null)){
						if(StringUtils.isNotBlank(field_comment)){
							create_sql +=  field_name+" "+"decimal"+"("+field_length+")"+" null comment '"+field_comment+"' ,";
						}else{
							create_sql += field_name+" "+"decimal"+"("+field_length+")"+" null";
						}
					}else{
						if(StringUtils.isNotBlank(is_null)){
							create_sql +=  field_name+" "+"decimal"+"("+field_length+")"+" not null comment '"+field_comment+"', ";
						}else{
							create_sql +=  field_name+" "+"decimal"+"("+field_length+")"+" not null";
						}
					}
					
					
				}else{
					if("true".equals(is_null)){
						if(StringUtils.isNotBlank(field_comment)){
							create_sql +=  field_name+" "+field_type+"("+field_length+")  null comment '"+field_comment+"' ,";
						}else{
							create_sql +=  field_name+" "+field_type+"("+field_length+") null";
						}
					}else{
						if(StringUtils.isNotBlank(is_null)){
							create_sql +=  field_name+" "+field_type+"("+field_length+") not null comment '"+field_comment+"' , ";
						}else{
							create_sql +=  field_name+" "+field_type+"("+field_length+") not null";
						}
					}
				}
				
			}
		}
		create_sql += "PRIMARY KEY (`id`)  ) ";
		Sql sql = Sqls.create(create_sql);
		dao().execute(sql);
		return true;
	}

}
